package com.chenailin.www.daoimpl;

import com.chenailin.www.dao.AppointmentDAO;
import com.chenailin.www.pojo.Appointment;
import com.chenailin.www.util.DBUtil;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class AppointmentDAOImpl implements AppointmentDAO {

    // 添加挂号记录 ???
    @Override
    public boolean insertAppointment(Appointment appointment) throws SQLException {
        String sql = "INSERT INTO appointments (department_id, student_id, doctor_id, date, schedule) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, appointment.getDepartmentId());
            ps.setInt(2, appointment.getStuId());
            ps.setInt(3, appointment.getDoctorId());
            ps.setDate(4, Date.valueOf(String.valueOf(appointment.getAppointmentTime())));
            ps.setInt(5, appointment.getSchedule());
            int affectedRows = ps.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return false;
        }
    }

    // 是否通过身份验证
    @Override
    public boolean isVerified(int userId) {
        String sql = "SELECT * FROM students WHERE id = ? AND verified = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setBoolean(2, true);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return false;
        }
    }

    // 删除挂号记录
    @Override
    public boolean deleteAppointment(int id) throws SQLException {
        String sql = "DELETE FROM appointments WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            int affectedRows = ps.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return false;
        }
    }

    // 标记医生为已选择
    @Override
    public boolean selectDoctor(int id, int slot) {
        String sql = "UPDATE doctor_schedule SET selected = ? WHERE id = ? AND schedule = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBoolean(1, true);
            ps.setInt(2, id);
            ps.setInt(3, slot);
            //受影响的行数
            int affectedRows = ps.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return false;
        }
    }

    // 标记医生为空闲
    public boolean unSelectDoctor(int id, int slot) {
        String sql = "UPDATE doctor_schedule SET selected = ? WHERE id = ? AND schedule = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBoolean(1, false);
            ps.setInt(2, id);
            ps.setInt(3, slot);
            //受影响的行数
            int affectedRows = ps.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return false;
        }
    }

    // 检查是否重复预约
    @Override
    public boolean hasConflict(int userId, int slot) {
        String sql = "SELECT * FROM appointments WHERE student_id = ? AND schedule = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, slot);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return false;
        }
    }

    // 查找挂号记录
    @Override
    public List<Appointment> findByUserId(int userId) {
        List<Appointment> list = new ArrayList<>();
        String sql = "SELECT * FROM appointments WHERE student_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()){
                while(rs.next()) {
                    list.add(new Appointment(rs.getDate("date").toLocalDate(),
                            rs.getInt("department_id"),
                            rs.getInt("doctor_id"),
                            rs.getInt("id"),
                            rs.getInt("schedule"),
                            rs.getInt("student_id")));
                }
                return list;
            }
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return null;
        }
    }

    // 查询挂号时间段
    @Override
    public int getSchedule(int id) {
        String sql = "SELECT schedule FROM appointments WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt("schedule");
            }
            return 0;
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return 0;
        }
    }

    // 根据编号查找挂号记录
    @Override
    public Appointment findById(int id) {
        String sql = "SELECT * FROM appointments WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()){
                if(rs.next()) {
                    return new Appointment(rs.getDate("date").toLocalDate(),
                            rs.getInt("student_id"),
                            getSchedule(rs.getInt("schedule")),
                            rs.getInt("id"),
                            rs.getInt("doctor_id"));
                }
                return null;
            }
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return null;
        }
    }

    // 查找每日病人数量
    @Override
    public int getDailyAppointments(int id, LocalDate date) {
        String sql = "SELECT COUNT(*) FROM appointments AS total WHERE DATE = ? AND doctor_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setDate(1, Date.valueOf(date));
            try (ResultSet rs = ps.executeQuery()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            System.err.println("数据库操作失败: " + e.getMessage());
            return 0;
        }
    }

}

